﻿CREATE PROCEDURE [dbo].[spCreateInventoryTransactionDetailsForPackage]
	@PackageID int
AS
set nocount on

declare c1 cursor local for select T1.InventoryTransactionID, (0-T1.Qty) - coalesce(T4.Qty, 0)
	from tblInventoryTransaction T1
		join tblInventory T2 on T1.InventoryID = T2.InventoryID
		join tblProduct T3 on T2.ProductID = T3.ProductID and T3.TrackIndividual = 1
		left outer join ( select InventoryTransactionID, count(*) as Qty
			from tblInventoryTransactionDetail 
			group by InventoryTransactionID ) as T4 on T1.InventoryTransactionID = T4.InventoryTransactionID
	where T1.PackageID = @PackageID
		and T1.InventoryTransactionTypeCode = 'S'
		and T1.InventoryTransactionStatusCode not in ( 'X' )
		and (0 - T1.Qty) > coalesce(T4.Qty, 0)

declare @InventoryTransactionID int
declare @Qty int
open c1
fetch next from c1 into @InventoryTransactionID, @Qty
while @@FETCH_STATUS = 0
begin
	declare @i int
	set @i = 0
	while @i < @Qty
	begin
		insert into tblInventoryTransactionDetail ( InventoryTransactionID ) values ( @InventoryTransactionID )
		set @i = @i + 1
	end

	fetch next from c1 into @InventoryTransactionID, @Qty
end
close c1
deallocate c1

RETURN 0